﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;
using System.Data.OracleClient;

namespace bdnt_pets_system_Bo.SystemManager
{
    public class EmployeeManager
    {
        //AccessHelperSQL accessHelper = AccessHelperSQL.GetInstance();
        AccessHelper accessHelper = AccessHelper.GetInstance();

        List<Tbl_Employee> ConvertTableToObjs(DataTable dt)
        {
            List<Tbl_Employee> returnlist = new List<Tbl_Employee>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_Employee
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    EmpNo = dr["EmpNo"] == DBNull.Value ? "" : dr["EmpNo"].ToString(),
                    EmpName = dr["EmpName"] == DBNull.Value ? "" : dr["EmpName"].ToString(),
                    Telephone = dr["Telephone"] == DBNull.Value ? "" : dr["Telephone"].ToString(),
                    Email = dr["Email"] == DBNull.Value ? "" : dr["Email"].ToString(),
                    DeptNo = dr["DeptNo"] == DBNull.Value ? "" : dr["DeptNo"].ToString(),
                    LoginName = dr["LoginName"] == DBNull.Value ? "" : dr["LoginName"].ToString(),
                    LoginPwd = dr["LoginPwd"] == DBNull.Value ? "" : dr["LoginPwd"].ToString(),
                    RoleStr = dr["RoleStr"] == DBNull.Value ? "" : dr["RoleStr"].ToString(),
                    CreateTime = dr["CreateTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["CreateTime"].ToString()),
                    LastTime = dr["LastTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["LastTime"].ToString())
                });
            }
            return returnlist;
        }

        public List<Tbl_Employee> GetObjListByCondition(string objName)
        {
            var sql = string.Format(@"select emp.*,
            RTRIM(replace(replace(replace(replace(replace(replace((
            select SYS_XMLAGG(SYS_XMLGEN(ro.roleName)) from Tbl_UserRole_Relation ur
            left join Tbl_Role ro on ro.Id=ur.roleId 
            where ur.userid=emp.id                
            ) ,'</ROLENAME>',','),'<ROLENAME>',''),'<?xml version=""1.0""?>',''),'<ROWSET>',''),'</ROWSET>',''),chr(10),''),',')
             RoleStr from Tbl_Employee emp");

            if (!string.IsNullOrEmpty(objName))
                sql = string.Format(@"select emp.*,
                RTRIM(replace(replace(replace(replace(replace(replace((
                select SYS_XMLAGG(SYS_XMLGEN(ro.roleName)) from Tbl_UserRole_Relation ur
                left join Tbl_Role ro on ro.Id=ur.roleId 
                where ur.userid=emp.id                
                ) ,'</ROLENAME>',','),'<ROLENAME>',''),'<?xml version=""1.0""?>',''),'<ROWSET>',''),'</ROWSET>','') ,chr(10),''),',')
                RoleStr from Tbl_Employee emp where emp.EmpName like '%{0}%'", objName);
            var dt = accessHelper.ExcuteDataTable(sql);
            return ConvertTableToObjs(dt);
        }

        public bool SaveObj(Tbl_Employee cuObj)
        {
            StringBuilder sql = new StringBuilder();
            //新增
            if (cuObj.Id == 0)
            {
                sql.AppendFormat(@"INSERT INTO Tbl_Employee
                               (EmpNo,EmpName,Email,Telephone,DeptNo,LoginName,LoginPwd,CreateTime,LastTime)
                                VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},{8})
                                ", cuObj.EmpNo, cuObj.EmpName, cuObj.Email, cuObj.Telephone,
                cuObj.DeptNo, cuObj.LoginName, cuObj.LoginPwd,
                (cuObj.CreateTime.HasValue && cuObj.CreateTime.Value.Year != 1900 ?
                "to_date('" + cuObj.CreateTime.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"),
                (cuObj.LastTime.HasValue && cuObj.LastTime.Value.Year != 1900 ?
                "to_date('" + cuObj.LastTime.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"));

                string sql1 = "select employee_seq.currval from dual";

                var cure = accessHelper.ExecuteScalar(sql.ToString(), sql1);
                if (cure == null)
                    return false;

                sql = new StringBuilder();

                if (!string.IsNullOrEmpty(cuObj.RoleIds))
                {
                    sql.AppendFormat(@"INSERT INTO Tbl_UserRole_Relation(UserId,RoleId)
                            select {0},Id from Tbl_Role where id in ({1});", Int32.Parse(cure.ToString()), cuObj.RoleIds);
                }
                if (!string.IsNullOrEmpty(cuObj.StationIds))
                {
                    sql.AppendLine(string.Format(@"INSERT INTO Tbl_EmpStations(EmpId,ToSeeStationId)
                        select {0},Id from Tbl_Station where id in ({1});", Int32.Parse(cure.ToString()), cuObj.StationIds));
                }

                if (cuObj.StationId != 0)
                {
                    sql.AppendLine(string.Format(@"INSERT INTO Tbl_StationMemebers(StationId,EmpId,IsManager)
                        values({0},{1},{2});", cuObj.StationId, Int32.Parse(cure.ToString()), cuObj.IsShopManager ? 1 : 0));
                }

                if (!string.IsNullOrEmpty(sql.ToString()))
                    return accessHelper.ExcultData(sql.ToString());
                return true;
            }
            else
            {
                sql = sql.AppendFormat(@"UPDATE Tbl_Employee
               SET EmpName = '{0}'
                ,Email = '{1}' 
                ,Telephone = '{2}' 
                ,DeptNo = '{3}' 
                ,LoginName = '{4}' 
                ,LoginPwd = '{5}'
                ,LastTime = {6} 
                 WHERE Id={7}", cuObj.EmpName, cuObj.Email, cuObj.Telephone, cuObj.DeptNo, cuObj.LoginName
                 , cuObj.LoginPwd, (cuObj.LastTime.HasValue && cuObj.LastTime.Value.Year != 1900 ?
                "to_date('" + cuObj.LastTime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss')" : "NULL"), cuObj.Id);

                var re = accessHelper.ExcultData(sql.ToString());
                if (re == true)
                {
                    sql = new StringBuilder();
                    sql = sql.AppendFormat(@"call UpdateUser('{0}',{1},'{2}',{3},{4})", cuObj.RoleIds, cuObj.Id, cuObj.StationIds,
                        cuObj.StationId, cuObj.IsShopManager ? 1 : 0);
                    return accessHelper.ExcultProc(sql.ToString());
                }
                return false;
            }
        }

        public Tbl_Employee GetObjById(int objId)
        {
            Tbl_Employee cur = new Tbl_Employee();
            var sql = string.Format(@"select rl.*,

RTRIM(replace(replace(replace(replace(replace(replace((
            select SYS_XMLAGG(SYS_XMLGEN(ur.roleId)) from Tbl_UserRole_Relation ur              
            where ur.userid=rl.id                
            ) ,'</ROLEID>',','),'<ROLEID>',''),'<?xml version=""1.0""?>',''),'<ROWSET>',''),'</ROWSET>',''),chr(10),''),',')
             RoleIds,

RTRIM(replace(replace(replace(replace(replace(replace((
            select SYS_XMLAGG(SYS_XMLGEN(es.ToSeeStationId)) from Tbl_EmpStations es              
            where es.empId=rl.Id                
            ) ,'</TOSEESTATIONID>',','),'<TOSEESTATIONID>',''),'<?xml version=""1.0""?>',''),'<ROWSET>',''),'</ROWSET>',''),chr(10),''),',')
             StationIds,

                sm.StationId,st.ShopId,sm.IsManager 
                from Tbl_Employee rl
                left join Tbl_StationMemebers sm on rl.Id=sm.EmpId
                left join Tbl_Station st on st.Id=sm.StationId where rl.Id={0}", objId);
            var dt = accessHelper.ExcuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                cur = new Tbl_Employee();

                cur.Id = Convert.ToInt32(dr["Id"]);
                cur.DeptNo = dr["DeptNo"] == DBNull.Value ? "" : dr["DeptNo"].ToString();
                cur.Email = dr["Email"] == DBNull.Value ? "" : dr["Email"].ToString();
                cur.EmpName = dr["EmpName"] == DBNull.Value ? "" : dr["EmpName"].ToString();
                cur.EmpNo = dr["EmpNo"] == DBNull.Value ? "" : dr["EmpNo"].ToString();
                cur.LoginName = dr["LoginName"] == DBNull.Value ? "" : dr["LoginName"].ToString();
                cur.LoginPwd = dr["LoginPwd"] == DBNull.Value ? "" : dr["LoginPwd"].ToString();
                cur.Telephone = dr["Telephone"] == DBNull.Value ? "" : dr["Telephone"].ToString();
                cur.CreateTime = dr["CreateTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["CreateTime"].ToString());
                cur.LastTime = dr["LastTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["LastTime"].ToString());
                cur.RoleIds = dr["RoleIds"] == DBNull.Value ? "" : dr["RoleIds"].ToString();
                cur.StationIds = dr["StationIds"] == DBNull.Value ? "" : dr["StationIds"].ToString();
                cur.StationId = dr["StationId"] == DBNull.Value ? 0 : Int32.Parse(dr["StationId"].ToString());
                cur.ShopId = dr["ShopId"] == DBNull.Value ? 0 : Int32.Parse(dr["ShopId"].ToString());
                cur.IsShopManager = dr["IsManager"] == DBNull.Value ? false : dr["IsManager"].ToString() == "1" ? true : false;
            }
            return cur;
        }

        public Tbl_Employee GetObjByNameAndPwd(string name, string pwd)
        {
            Tbl_Employee cur = new Tbl_Employee();
            var sql = string.Format(@"select rl.*,


RTRIM(replace(replace(replace(replace(replace(replace((
            select SYS_XMLAGG(SYS_XMLGEN(ur.roleId)) from Tbl_UserRole_Relation ur              
            where ur.userid=rl.id                
            ) ,'</ROLEID>',','),'<ROLEID>',''),'<?xml version=""1.0""?>',''),'<ROWSET>',''),'</ROWSET>',''),chr(10),''),',')
             RoleIds,
 
RTRIM(replace(replace(replace(replace(replace(replace((
            select SYS_XMLAGG(SYS_XMLGEN(ro.roleName)) from Tbl_UserRole_Relation ur    
             left join Tbl_Role ro on ro.Id=ur.roleId           
            where ur.userid=rl.id                
            ) ,'</ROLENAME>',','),'<ROLENAME>',''),'<?xml version=""1.0""?>',''),'<ROWSET>',''),'</ROWSET>',''),chr(10),''),',')
             RoleStr,
                 
RTRIM(replace(replace(replace(replace(replace(replace((
            select SYS_XMLAGG(SYS_XMLGEN(es.ToSeeStationId)) from Tbl_EmpStations es              
            where es.empId=rl.Id                
            ) ,'</TOSEESTATIONID>',','),'<TOSEESTATIONID>',''),'<?xml version=""1.0""?>',''),'<ROWSET>',''),'</ROWSET>',''),chr(10),''),',')
             StationIds 

                from Tbl_Employee rl where LoginName='{0}' and LoginPwd='{1}'", name, pwd);
            var dt = accessHelper.ExcuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                cur = new Tbl_Employee
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    DeptNo = dr["DeptNo"] == DBNull.Value ? "" : dr["DeptNo"].ToString(),
                    Email = dr["Email"] == DBNull.Value ? "" : dr["Email"].ToString(),
                    EmpName = dr["EmpName"] == DBNull.Value ? "" : dr["EmpName"].ToString(),
                    EmpNo = dr["EmpNo"] == DBNull.Value ? "" : dr["EmpNo"].ToString(),
                    LoginName = dr["LoginName"] == DBNull.Value ? "" : dr["LoginName"].ToString(),
                    LoginPwd = dr["LoginPwd"] == DBNull.Value ? "" : dr["LoginPwd"].ToString(),
                    Telephone = dr["Telephone"] == DBNull.Value ? "" : dr["Telephone"].ToString(),
                    CreateTime = dr["CreateTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["CreateTime"].ToString()),
                    LastTime = dr["LastTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["LastTime"].ToString()),
                    RoleIds = dr["RoleIds"] == DBNull.Value ? "" : dr["RoleIds"].ToString(),
                    StationIds = dr["StationIds"] == DBNull.Value ? "" : dr["StationIds"].ToString(),
                    RoleStr = dr["RoleStr"] == DBNull.Value ? "" : dr["RoleStr"].ToString()
                };
            }
            return cur;
        }

        public bool DeleteCuObj(Int32 objId)
        {
            string sql = string.Format(@"call deleteuser({0})", objId);
            return accessHelper.ExcultProc(sql);
        }
    }
}
